package sqldbManagement.dblibrary;



import java.sql.Timestamp;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Scanner;

import sqldbManagement.dblibrary.entity.Book;
import sqldbManagement.dblibrary.cj.model.Code;
import sqldbManagement.dblibrary.cj.service.JDBCService;
import sqldbManagement.dblibrary.entity.Card;
import sqldbManagement.dblibrary.entity.vo.BookANDCard;
import sqldbManagement.dblibrary.entity.vo.BookSUM;



public class Main {
    public static void main(String[] args) {
        System.out.println("1. 进书");
        System.out.println("2. 办理借书卡");
        System.out.println("3. 借书");
        System.out.println("4. 还书");
        System.out.println("5. 查询书本借出记录");
        System.out.println("6. 查询借书证借出记录");
        System.out.println("7. 显示图书列表（按借阅次序）");
        System.out.println("0. 退出系统");
        Scanner scanner = new Scanner(System.in);
        while(true){
            switch (scanner.nextInt()){
                case 1:
                    addBook(scanner);
                    break;
                case 2:
                    addCard(scanner);
                    break;
                case 3:
                    lendBook(scanner);
                    break;
                case 4:
                    sendBook(scanner);
                    break;
                case 5:
                    lendBookHis(scanner);
                    break;
                case 6:
                    lendCardHis(scanner);
                    break;
                case 7:
                    sum(scanner);
                    break;
                case 0:
                	System.out.println("已经退出系统！");
                	return;
            }
        }
    }

    private static void sum(Scanner scanner) {
        List<BookSUM> query = JDBCService.query("SELECT DISTINCT bookName NAME,msg\n" +
                "FROM book\n" +
                "LEFT JOIN\n" +
                "(SELECT \n" +
                "book_id,COUNT(*) msg\n" +
                "FROM book_card\n" +
                "GROUP BY book_id\n" +
                "ORDER BY msg DESC) t\n" +
                "ON id = book_id", null, BookSUM.class);
        for (BookSUM bookSUM : query) {
            System.out.println("《"+bookSUM.getName()+"》\t被借出 " + bookSUM.getMsg() +" 次");
        }
    }

    private static void lendCardHis(Scanner scanner) {
        System.out.println("输入借卡名");
        String cardName = scanner.next();
        Card card = JDBCService.queryOne("select * from card where cardName = ?",
                JDBCService.getModel().add(Code.STRING, cardName), Card.class);
        if(card == null){
            System.out.println("该书不存在");
            return;
        }

        List<BookANDCard> bac = JDBCService.query("select * from book_card where card_id = ?",
                JDBCService.getModel().add(Code.INT, card.getId()), BookANDCard.class);

        HashMap<Integer,Book> map = new HashMap<>();

        for (BookANDCard bookANDCard : bac) {
            Book book = map.get(bookANDCard.getBook_id());
            if(book == null){
                Book one = JDBCService.queryOne("select * from book where id = ?",
                        JDBCService.getModel().add(Code.INT, bookANDCard.getBook_id()), Book.class);
                map.put(one.getId(),one);
                book = one;
            }
            System.out.print("在 "+bookANDCard.getTime()+" 借 《"+ book.getBookName() +"》 书，");
            System.out.println(bookANDCard.getRet()? "已归还" : "未归还");
        }
    }

    private static void lendBookHis(Scanner scanner) {
        System.out.println("输入书名");
        String bookName = scanner.next();
        Book book = JDBCService.queryOne("select * from book where bookName = ?",
                JDBCService.getModel().add(Code.STRING, bookName), Book.class);
        if(book == null){
            System.out.println("该书不存在");
            return;
        }

        List<BookANDCard> bac = JDBCService.query("select * from book_card where book_id = ?",
                JDBCService.getModel().add(Code.INT, book.getId()), BookANDCard.class);

        HashMap<Integer,Card> map = new HashMap<>();

        for (BookANDCard bookANDCard : bac) {
            Card card = map.get(bookANDCard.getCard_id());
            if(card == null){
                Card one = JDBCService.queryOne("select * from card where id = ?",
                        JDBCService.getModel().add(Code.INT, bookANDCard.getCard_id()), Card.class);
                map.put(one.getId(),one);
                card = one;
            }
            System.out.print(card.getCardName()+"在 "+bookANDCard.getTime()+" 借此书，");
            System.out.println(bookANDCard.getRet()? "已归还" : "未归还");
        }
    }

    private static void sendBook(Scanner scanner) {
        System.out.println("输入书名");
        String bookName = scanner.next();
        Book book = JDBCService.queryOne("select * from book where bookName = ?",
                JDBCService.getModel().add(Code.STRING, bookName), Book.class);
        if(book == null){
            System.out.println("该书不存在");
            return;
        }

        System.out.println("输入借书卡人姓名");
        String cardName = scanner.next();
        Card card = JDBCService.queryOne("select * from card where cardName = ?",
                JDBCService.getModel().add(Code.STRING, cardName), Card.class);
        if(card == null){
            System.out.println("没有此借书卡");
            return;
        }

        int update = JDBCService.update("update book_card set ret = ? where book_id = ? AND card_id = ?",
                JDBCService.getModel().add(Code.BOOLEAN,true).add(Code.INT, book.getId()).add(Code.INT,card.getId()));
        if(update == 0){
            System.out.println("还书失败");
            return;
        }

        JDBCService.update("update book set bookSize = ? where id = ?",
                JDBCService.getModel().add(Code.INT,book.getBookSize()+1).add(Code.INT,book.getId()));

        System.out.println("还书成功");
    }

    private static void lendBook(Scanner scanner) {
        System.out.println("输入书名");
        String bookName = scanner.next();
        Book book = JDBCService.queryOne("select * from book where bookName = ? AND bookSize > 0",
                JDBCService.getModel().add(Code.STRING, bookName), Book.class);
        if(book == null){
            System.out.println("该书无库存");
            return;
        }

        System.out.println("输入借书卡人姓名");
        String cardName = scanner.next();
        Card card = JDBCService.queryOne("select * from card where cardName = ?",
                JDBCService.getModel().add(Code.STRING, cardName), Card.class);
        if(card == null){
            System.out.println("没有此借书卡");
            return;
        }

        BookANDCard bookANDCard = JDBCService.queryOne("select * from book_card where book_id = ? AND card_id = ? AND ret = 0",
                JDBCService.getModel().add(Code.INT, book.getId()).add(Code.INT, card.getId()), BookANDCard.class);
        if(bookANDCard != null){
            System.out.println("已借出,不能再借");
            return;
        }

        int insert = JDBCService.insert("insert into book_card(book_id,card_id,ret,time) value (?,?,?,?)",
                JDBCService.getModel().add(Code.INT, book.getId()).add(Code.INT,card.getId()).add(Code.BOOLEAN,false).add(Code.TIMESTAMP,new Timestamp(new Date().getTime())));
        if(insert == 0){
            System.out.println("借书失败");
            return;
        }

        JDBCService.update("update book set bookSize = ? where id = ?",
                JDBCService.getModel().add(Code.INT,book.getBookSize()-1).add(Code.INT,book.getId()));

        System.out.println("借书成功");

    }

    private static void addCard(Scanner scanner) {
        System.out.println("输入借书卡人姓名");
        String name = scanner.next();

        int insert = JDBCService.insert("insert into card(cardName) value (?)",
                JDBCService.getModel().add(Code.STRING, name));

        if(insert == 0)
            System.out.println("添加失败");
        else
            System.out.println("添加成功");
    }

    private static void addBook(Scanner scanner) {
        System.out.println("输入书名");
        String name = scanner.next();
        System.out.println("输入数量");
        int size = scanner.nextInt();

        int insert = JDBCService.insert("insert into book(bookName,bookSize) value (?,?)",
                JDBCService.getModel().add(Code.STRING, name).add(Code.INT, size));

        if(insert == 0)
            System.out.println("添加失败");
        else
            System.out.println("添加成功");
    }
}
